// From Firebird, reviewed

CREATE TABLE DOLL 
(
  DOLL_ID     NVARCHAR(32) NOT NULL,
  DOLL_NAME   NVARCHAR(40) NOT NULL,
  DATE_BOUGHT DATE,
  HAIR_ID     NVARCHAR(32),
  DRESS_SIZE  NVARCHAR(4),
  MOM_ID      NVARCHAR(32) NOT NULL,
  ASPECT      NVARCHAR(1024),
  PICTURE     IMAGE
);
CREATE TABLE GIRL 
(
  GIRL_ID     NVARCHAR(32) NOT NULL,
  GIRL_NAME   NVARCHAR(40) NOT NULL,
  AGE         INTEGER,
  HAIR_ID     NVARCHAR(32) NOT NULL,
  PHONE       NVARCHAR(16) 
);
CREATE TABLE HAIR 
(
  HAIR_ID     NVARCHAR(32) NOT NULL,
  HAIR_COLOR  NVARCHAR(80) NOT NULL
);
CREATE TABLE INVITATION 
(
  INVITATION_ID NVARCHAR(32) NOT NULL,
  PARTY_ID      NVARCHAR(32) NOT NULL,
  INVITEE_ID    NVARCHAR(32) NOT NULL,
  ACCEPTED      BIT
);
CREATE TABLE KITTO_USERS 
(
  USER_NAME     NVARCHAR(32) NOT NULL,
  PASSWORD_HASH NVARCHAR(32),
  IS_ACTIVE     BIT
);
CREATE TABLE PARTY 
(
  PARTY_ID      NVARCHAR(32) NOT NULL,
  PARTY_NAME    NVARCHAR(40) NOT NULL,
  PARTY_DATE    DATE         NOT NULL,
  PARTY_TIME    TIME         NOT NULL,
  ADDRESS       NVARCHAR(256) NOT NULL
);

ALTER TABLE DOLL ADD CONSTRAINT PK_DOLL PRIMARY KEY (DOLL_ID);
ALTER TABLE GIRL ADD CONSTRAINT PK_GIRL PRIMARY KEY (GIRL_ID);
ALTER TABLE HAIR ADD CONSTRAINT PK_HAIR PRIMARY KEY (HAIR_ID);
ALTER TABLE INVITATION ADD CONSTRAINT PK_INVITATION PRIMARY KEY (INVITATION_ID);
ALTER TABLE KITTO_USERS ADD CONSTRAINT PK_KITTO_USERS PRIMARY KEY (USER_NAME);
ALTER TABLE PARTY ADD CONSTRAINT PK_PARTY PRIMARY KEY (PARTY_ID);

ALTER TABLE DOLL ADD CONSTRAINT DT_DOLL_MOM 
  FOREIGN KEY (MOM_ID) REFERENCES GIRL
  (GIRL_ID) 
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
;

ALTER TABLE DOLL ADD CONSTRAINT FK_DOLL_HAIR 
  FOREIGN KEY (HAIR_ID) REFERENCES HAIR
  (HAIR_ID) 
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
;

ALTER TABLE GIRL ADD CONSTRAINT FK_GIRL_HAIR 
  FOREIGN KEY (HAIR_ID) REFERENCES HAIR
  (HAIR_ID) 
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
;

ALTER TABLE INVITATION ADD CONSTRAINT DT_INVITATION_PARTY 
  FOREIGN KEY (PARTY_ID) REFERENCES PARTY
  (PARTY_ID) 
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
;

ALTER TABLE INVITATION ADD CONSTRAINT FK_INVITATION_GIRL 
  FOREIGN KEY (INVITEE_ID) REFERENCES GIRL
  (GIRL_ID) 
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
;

// From SQL Server, extracted

CREATE TABLE dbo.DOLL (
    DOLL_ID NVarChar(32) NOT NULL,
    DOLL_NAME NVarChar(40) NOT NULL,
    DATE_BOUGHT Date,
    HAIR_ID NVarChar(32),
    DRESS_SIZE NVarChar(4),
    MOM_ID NVarChar(32) NOT NULL,
    ASPECT NVarChar(1024),
    PICTURE Image, 
    CONSTRAINT PK_DOLL PRIMARY KEY CLUSTERED (
      DOLL_ID
    )
)
GO
CREATE TABLE dbo.GIRL (
    GIRL_ID NVarChar(32) NOT NULL,
    GIRL_NAME NVarChar(40) NOT NULL,
    AGE Int,
    HAIR_ID NVarChar(32) NOT NULL,
    PHONE NVarChar(16), 
    CONSTRAINT PK_GIRL PRIMARY KEY CLUSTERED (
      GIRL_ID
    )
)
GO
CREATE TABLE dbo.HAIR (
    HAIR_ID NVarChar(32) NOT NULL,
    HAIR_COLOR NVarChar(80) NOT NULL, 
    CONSTRAINT PK_HAIR PRIMARY KEY CLUSTERED (
      HAIR_ID
    )
)
GO
CREATE TABLE dbo.INVITATION (
    INVITATION_ID NVarChar(32) NOT NULL,
    PARTY_ID NVarChar(32) NOT NULL,
    INVITEE_ID NVarChar(32) NOT NULL,
    ACCEPTED Bit, 
    CONSTRAINT PK_INVITATION PRIMARY KEY CLUSTERED (
      INVITATION_ID
    )
)
GO
CREATE TABLE dbo.KITTO_USERS (
    USER_NAME NVarChar(32) NOT NULL,
    PASSWORD_HASH NVarChar(32),
    IS_ACTIVE Bit, 
    CONSTRAINT PK_KITTO_USERS PRIMARY KEY CLUSTERED (
      USER_NAME
    )
)
GO
CREATE TABLE dbo.PARTY (
    PARTY_ID NVarChar(32) NOT NULL,
    PARTY_NAME NVarChar(40) NOT NULL,
    PARTY_DATE Date NOT NULL,
    PARTY_TIME Time NOT NULL,
    ADDRESS NVarChar(256) NOT NULL, 
    CONSTRAINT PK_PARTY PRIMARY KEY CLUSTERED (
      PARTY_ID
    )
)
GO

ALTER TABLE dbo.DOLL WITH CHECK ADD CONSTRAINT DT_DOLL_MOM FOREIGN KEY (MOM_ID)
  REFERENCES dbo.GIRL (GIRL_ID)
  ON DELETE NO ACTION 
  ON UPDATE NO ACTION
GO

ALTER TABLE dbo.INVITATION WITH CHECK ADD CONSTRAINT DT_INVITATION_PARTY FOREIGN KEY (PARTY_ID)
  REFERENCES dbo.PARTY (PARTY_ID)
  ON DELETE NO ACTION 
  ON UPDATE NO ACTION
GO

ALTER TABLE dbo.DOLL WITH CHECK ADD CONSTRAINT FK_DOLL_HAIR FOREIGN KEY (HAIR_ID)
  REFERENCES dbo.HAIR (HAIR_ID)
  ON DELETE NO ACTION 
  ON UPDATE NO ACTION
GO

ALTER TABLE dbo.GIRL WITH CHECK ADD CONSTRAINT FK_GIRL_HAIR FOREIGN KEY (HAIR_ID)
  REFERENCES dbo.HAIR (HAIR_ID)
  ON DELETE NO ACTION 
  ON UPDATE NO ACTION
GO

ALTER TABLE dbo.INVITATION WITH CHECK ADD CONSTRAINT FK_INVITATION_GIRL FOREIGN KEY (INVITEE_ID)
  REFERENCES dbo.GIRL (GIRL_ID)
  ON DELETE NO ACTION 
  ON UPDATE NO ACTION
GO
